<%@ page contentType="text/html; charset=UTF-8" language="java" %>
<%@ page import="com.base.myproject.server.tools.*" %>
<%@ page import="com.base.myproject.client.jdbc.DataSet,java.util.Date" %>
<%@page import="java.text.SimpleDateFormat,java.util.Calendar,java.math.BigDecimal"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<title>员工月份工资明细比较</title>
<LINK href="report.css" rel=stylesheet>
</head>
<body>
<%
	String beforeIcode = request.getParameter("beforeIcode");
	String afterIcode = request.getParameter("afterIcode");
	String employno = request.getParameter("employno");
	DataSetTool DST = new DataSetTool();
	
	
	String beforeUpcodeSql = "select * from hr_salary_main_c where icode = " + beforeIcode + "";
	String afterUpcodeSql = "select * from hr_salary_main_c where icode = " + afterIcode + "";
	String employeeChangeSql = "select stuff((select ',' + remark from dbo.hr_EmployeeChange  " + 
								"where bcode = substring('" + employno + "',5,4) and ChangeDate > (select end_time from hr_salary_main_c where icode = '" + beforeIcode + "')  " + 
								"and ChangeDate <= (select end_time from hr_salary_main_c where icode = '" + afterIcode + "') group by remark FOR XML PATH('')), 1, 1, '')  " + 
								"as type";
	String salarySql = "select employno,noName,sumSalary1,sumSalary2,(sumSalary2 - sumSalary1) as value from ( " + 
						"select '" + employno + "' as employno,sumSalary1,sumSalary2, beforeMonthSalary.allowanceno_,noName from ( " + 
						"	select amount as sumSalary1, allowanceno_ from hr_salary, hr_salary_main_c where upcode = code and hr_salary_main_c.icode = '" + beforeIcode + "' and bcode = substring('" + employno + "',5,4) " + 
						") as beforeMonthSalary " + 
						"inner join (  " + 
						"	select amount as sumSalary2, allowanceno_ from hr_salary, hr_salary_main_c where upcode = code and hr_salary_main_c.icode = '" + afterIcode + "' and bcode = substring('" + employno + "',5,4) " + 
						") as afterMonthSalary on sumSalary1 != sumSalary2 and beforeMonthSalary.allowanceno_ = afterMonthSalary.allowanceno_ " + 
						"inner join (" +
						"		select desc_cn as noName, code from hr_salary_item" +
						"	) as item on item.code = beforeMonthSalary.allowanceno_ " + 
						"union all  " + 
						"select '" + employno + "' as employno,sumSalary1,0 as sumSalary2, beforeMonthSalary.allowanceno_,noName from ( " + 
						"	select amount as sumSalary1, allowanceno_ from hr_salary salary, hr_salary_main_c where upcode = code and hr_salary_main_c.icode = '" + beforeIcode + "' and bcode = substring('" + employno + "',5,4) " + 
						"and not exists(select * from hr_salary, hr_salary_main_c where upcode = code and hr_salary_main_c.icode = '" + afterIcode + "' and bcode = substring('" + employno + "',5,4) and allowanceno_ = salary.allowanceno_) " + 
						") as beforeMonthSalary " + 
						"inner join (" +
						"		select desc_cn as noName, code from hr_salary_item" +
						"	) as item on item.code = beforeMonthSalary.allowanceno_ " + 
						"where sumSalary1 != 0 " + 
						"union all " + 
						" " + 
						"select '" + employno + "' as employno,0 as sumSalary1,sumSalary2, afterMonthSalary.allowanceno_,noName from ( " + 
						"	select amount as sumSalary2, allowanceno_ from hr_salary salary, hr_salary_main_c where upcode = code and hr_salary_main_c.icode = '" + afterIcode + "' and bcode = substring('" + employno + "',5,4) " + 
						"and not exists(select * from hr_salary, hr_salary_main_c where upcode = code and hr_salary_main_c.icode = '" + beforeIcode + "' and bcode = substring('" + employno + "',5,4) and allowanceno_ = salary.allowanceno_) " + 
						") as afterMonthSalary " + 
						"inner join (" +
						"		select desc_cn as noName, code from hr_salary_item" +
						"	) as item on item.code = afterMonthSalary.allowanceno_ " + 
						"where sumSalary2 != 0 " +
						") as a";
	
	DataSet beforeUpcodeRs = DST.getDataSet(beforeUpcodeSql);
	DataSet afterUpcodeRs = DST.getDataSet(afterUpcodeSql);
	DataSet employeeChangeRs = DST.getDataSet(employeeChangeSql);
	DataSet salaryRs = DST.getDataSet(salarySql);
	
	String beforeCode = beforeUpcodeRs.getValue(0, "code");
	String afterCode = afterUpcodeRs.getValue(0, "code");
%>
<div>
	<table id="tableExcel" style="border-collapse: collapse" width="100%"
		border="1" cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC"
		bordercolorlight="#CCCCCC"
		style="line-height: 120%;border-color:#000000">
		<% if (employeeChangeRs.getValue(0, "type") != null) { %>
		<thead>编号为<%= employno %>的人员从<%= beforeCode %> 到 <%= afterCode %>由于<%= employeeChangeRs.getValue(0, "type") %>引起的薪金变动如下</thead>
		<% } %>
		<tr bgcolor="#e8e8e0" align="center">
			<td>人员编码</td>
			<td>薪金项目</td>
			<td><%= beforeCode %>金额</td>
			<td><%= afterCode %>金额</td>
			<td>差额</td>
		</tr>
		
		<% for (int i = 0; i < salaryRs.getRowCount(); i++) { %>
			<tr>
				<td><%= salaryRs.getValue(i, "employno") %></td>
				<td><%= salaryRs.getValue(i, "noName") %></td>
				<td><%= RPTString2Double.string2String(salaryRs.getValue(i, "sumSalary1")) %></td>
				<td><%= RPTString2Double.string2String(salaryRs.getValue(i, "sumSalary2")) %></td>
				<td><%= RPTString2Double.string2String(salaryRs.getValue(i, "value")) %></td>
			</tr>
		<% } %>
	</table>
</div>
</body>
</html>